﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace DotNetDal.Core.Data
{
    public class Query
    {
        public enum Comparison
        {
            Equals,
            NotEquals,
            Like,
            NotLike,
            GreaterThan,
            GreaterOrEquals,
            LessThan,
            LessOrEquals,
        }



        public enum Operator
        {
            And,
            Or
        }

        private DALRecord _record;
        private Query _parentQuery;
        private List<SearchCondition> _searchConditions = new List<SearchCondition>();

        public Query(DALRecord record)
        {
            this._record = record;

        }

        public Query AddWhere(string columnName, Comparison comparison, object value)
        {
            _searchConditions.Add(new SearchCondition(null, columnName, comparison, value));

            return this;
        }
        public Query AddAnd(string columnName, Comparison comparison, object value)
        {
            _searchConditions.Add(new SearchCondition(Operator.And, columnName, comparison, value));

            return this;
        }
        public Query AddOr(string columnName, Comparison comparison, object value)
        {
            _searchConditions.Add(new SearchCondition(Operator.Or, columnName, comparison, value));

            return this;
        }
        public Query AddLeftBracket(Operator op, string columnName, Comparison comparison, object value)
        {
            Query q = new Query(_record);
            q._parentQuery = this;
            q._searchConditions.Add(new SearchCondition(op, columnName, comparison, value));

            _searchConditions.Add(new SearchCondition(q));

            return q;
        }
        public Query AddRightBracket()
        {
            return _parentQuery;
        }

        internal class SearchCondition
        {
            internal Operator Operator { get; set; }
            internal string ColumnName { get; set; }
            internal Comparison Comparison { get; set; }
            internal object Value { get; set; }
            internal Query InnerQuery { get; set; }

            internal SearchCondition(System.Nullable<Operator> op, string columnName, Comparison comparison, object value)
            {
                ColumnName = columnName;
                Comparison = comparison;
                Value = value;
            }

            internal SearchCondition(Query q)
            {
                InnerQuery = q;
            }
        }

        public string GetComparisonString(Comparison comparison)
        {
            switch (comparison)
            {
                case Comparison.Equals:
                    return " = ";
                case Comparison.NotEquals:
                    return " <> ";
                case Comparison.Like:
                    return " LIKE ";
                case Comparison.NotLike:
                    return " NOT LIKE ";
                case Comparison.GreaterThan:
                    return " > ";
                case Comparison.GreaterOrEquals:
                    return " >= ";
                case Comparison.LessThan:
                    return " < ";
                case Comparison.LessOrEquals:
                    return " <= ";
                default:
                    return "";
            }
        }

        public DALRecord[] FetchResults()
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            string sql = @"select * from [" + this._record.SchemaName + "].[" + this._record.ClassName + "] ";

            sql = CreateConditionSql(this, parameters);

            return DALRecord.GetFromSql<DALRecord>(sql, parameters.ToArray());
        }

        private string CreateConditionSql(Query q, List<SqlParameter> parameters)
        {
            string whereSql = "";

            for (int i = 0; i < q._searchConditions.Count; i++)
            {
                SearchCondition search = q._searchConditions[i];

                if (search.InnerQuery != null)
                {
                    whereSql += CreateConditionSql(search.InnerQuery, parameters);
                }
                else
                {
                    if (i == 0 && q._parentQuery == null)
                        whereSql += " where ";

                    if (i == 0 && q._parentQuery != null)
                        whereSql += " " + search.Operator + " ( ";

                    if (i > 0)
                        whereSql += " " + search.Operator + " ";

                    whereSql += " [" + search.ColumnName + "] " + GetComparisonString(search.Comparison) + " @" + search.ColumnName;

                    if (i == q._searchConditions.Count - 1 && q._parentQuery != null)
                        whereSql += " ) ";

                    parameters.Add(new SqlParameter("@" + search.ColumnName, search.Value));
                }
            }

            return whereSql;

            //    

            //    if (parameters.Count == 1)
            //        sql += " where ";
            //    else
            //        sql += " " + search.Operator.ToString() + " ";

            //    sql += " [" + search.ColumnName + "] " + GetComparisonString(search.Comparison) + " @" + search.ColumnName;
            //}

            //foreach (Query subQuery in q._queries)
            //{
            //    sql += CreateConditionSql(subQuery, parameters, sql);
            //}

            //return sql;
        }
    }
}